from openpyxl.workbook import Workbook
from openpyxl.styles import Alignment,Font,Border,Side,PatternFill
from openpyxl.utils import get_column_letter
# #将文档内容写入一个单元格
# with open("user.txt",mode="r",encoding="utf-8") as f:
#      content=f.read()
#      # print(content)
# wb=Workbook()
# ws=wb.worksheets[0]
# ws.title="从txt导入的数据"
# c0=ws.cell(1,1)
# c0.value=content
# wb.save("files/x1.xlsx")


# # 将文档内容写入多行单元格
# wb=Workbook()
# ws=wb.worksheets[0]
# ws.title="从txt导入的数据写入多行"
#
# with open("user.txt",mode="r",encoding="utf-8") as f:
#      lines=f.readlines()
#      # print(len(lines))
#      for row,line in enumerate(lines,1):
#           cx=ws.cell(row,1)
#           cx.value=line.strip()
#           # print(row,line.strip())
# wb.save("files/x2.xlsx")


# # 将文档内容写入多列单元格
# wb=Workbook()
# ws=wb.worksheets[0]
# ws.title="从txt导入的数据写入多列"
# with open("user.txt",mode="r",encoding="utf-8") as f:
#      lines=f.readlines()
#      for col,line in enumerate(lines,1):
#           cx=ws.cell(1,col)
#           cx.value=line.strip()
# wb.save("files/x3.xlsx")



wb=Workbook()
ws=wb.worksheets[0]
ws.title="从txt导入的数据写入多行多列"
BORDER=Border(
     top=Side(style="thin",color="FF00FF"),
     bottom=Side(style="thin",color="00FF00"),
     left=Side(style="thin",color="8B658B"),
     right=Side(style="thin",color="B22222")
)
#大标题（合并单元格）
c1=ws.cell(1,1)
c1.value="人员信息"
c1.alignment=Alignment(horizontal="center",vertical="center")
c1.font=Font(color="FF1493", sz=20, name="黑体")
c1.border=BORDER
c1.fill=PatternFill("solid",fgColor="7FFF00")
ws.row_dimensions[1].height=50          #设置工作表中指定行的行高。
ws.merge_cells(start_row=1,start_column=1,end_row=1,end_column=3) #合并单元格
#小标题及格式
header_list =["姓名","性别","年龄"]
for col,item in enumerate(header_list,1):
     cx=ws.cell(2,col)
     cx.value=item
     cx.alignment=Alignment(horizontal="left",vertical="top")
     cx.font = Font(color="00FF00", sz=16, name="楷体")
     cx.border=BORDER
     cx.fill=PatternFill("solid",fgColor="FFF0F5")

# ws.cell(1,1).value="姓名"
# ws.cell(1,1).alignment=Alignment(horizontal="left",vertical="top")   #设置单元格内容的水平和垂直对齐方式。
# ws.cell(1,1).font=Font(color="00FF00",sz=16,name="楷体")              #设置字体的颜色，大小，名称。
# ws.cell(1,1).border=BORDER                                           #设置单元格的四条边框线的样式和颜色。
# ws.cell(1,1).fill=PatternFill("solid",fgColor="FFF0F5")   #设置单元格的填充颜色。
#
# ws.cell(1,2).value="性别"
# ws.cell(1,2).alignment=Alignment(horizontal="right",vertical="bottom")
# ws.cell(1,2).font=Font(color="0000FF",size=18,name="微软雅黑")
# ws.cell(1,2).border=BORDER
# ws.cell(1,2).fill=PatternFill("lightDown",bgColor="F0FFF0")
#
# ws.cell(1,3).value="年龄"
# ws.cell(1,3).alignment=Alignment(horizontal="center",vertical="distributed")
# ws.cell(1,3).font=Font(color="FFFF00",size=20,name="仿宋")
# ws.cell(1,3).border=BORDER
# ws.cell(1,3).fill=PatternFill("solid",fgColor="FF00FF")

#设置列宽
for idx in range(1,4):
     location=get_column_letter(idx)
     ws.column_dimensions[location].width=20
# ws.column_dimensions["A"].width=20      #设置工作表中指定列的列宽。
# ws.column_dimensions["B"].width=10      #设置工作表中指定列的列宽。
# ws.column_dimensions["C"].width=40      #设置工作表中指定列的列宽。

#数据信息
with open("user.txt",mode="r",encoding="utf-8") as f:
     lines=f.readlines()
     for row,line in enumerate(lines,3):
          line_data=line.strip().split(",")
          # print(row,line_data)
          for col,data in enumerate(line_data,1):
               print(row,col,data.strip())
               cx=ws.cell(row,col)
               cx.value=data.strip()
               cx.border=BORDER
wb.save("files/x6.xlsx")

